//
//  SQLHelper.m
//  LoveHealth
//
//  Created by 陈永坤 on 14-9-25.
//  Copyright (c) 2014年 lanou. All rights reserved.
//

#import "SQLHelper.h"


static SQLHelper * helper = nil;



@implementation SQLHelper

+ (SQLHelper *)shareInstance
{
    if (!helper) {
        helper = [[SQLHelper alloc]init];
        
        //打开数据库
        NSString * documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
        NSString * sqlitePath = [documentPath stringByAppendingPathComponent:@"loveHealth.sqlite"];
        helper.db = [FMDatabase databaseWithPath:sqlitePath];
        [helper createTable];
        
    }
    return helper;
}



- (void)createTable
{
    if ([self.db open]) {
        
        
        NSString * sql1 = [NSString stringWithFormat:@"CREATE TABLE %@ (%@ INTEGER PRIMARY KEY, %@ TEXT, %@ BLOB);",KnewsTableName,KID,Ktitle,Kdata];
        NSString * sql2 = [NSString stringWithFormat:@"CREATE TABLE %@ (%@ INTEGER PRIMARY KEY, %@ TEXT, %@ BLOB);",KfoodTableName,KID,Ktitle,Kdata];
        NSString * sql3 = [NSString stringWithFormat:@"CREATE TABLE %@ (%@ INTEGER PRIMARY KEY, %@ TEXT, %@ BLOB);",KknowledgeTableName,KID,Ktitle,Kdata];
        
        BOOL res1 = [self.db executeUpdate:sql1];
        BOOL res2 = [self.db executeUpdate:sql2];
        BOOL res3 = [self.db executeUpdate:sql3];
        if (!res1&&!res2 &&res3) {
            NSLog(@"error when creating db table");
        } else {
            NSLog(@"success to creating db table");
        }
        [self.db close];
    }
}

- (BOOL)insertWithValue:(id)value tableName:(NSString *)tableName
{
    BOOL res = NO;
    
    if ([self.db open]) {
    
        if ([tableName isEqualToString:KnewsTableName]) {
            
            
            HealthNewsDetail * newsDetail = (HealthNewsDetail *)value;
            
            NSData * data = [self archiver:newsDetail];
            //NSString * insertSql = [NSString stringWithFormat:@"INSERT INTO '%@' ('%@', '%@', '%@' )VALUES (%ld, '%@', '%@' )",tableName,KID,Ktitle,Kdata,[newsDetail.ID integerValue],newsDetail.title,data];
           
            //[self.db executeUpdate:@"INSERT INTO news (id, title,data)VALUES (?,?,?)",[[newsDetail.ID intValue],newsDetail.title,data];
            res = [self.db executeUpdate:@"INSERT INTO news (id, title,data)VALUES (?,?,?)",newsDetail.ID,newsDetail.title,data];
        
        }
        if ([tableName isEqualToString:KfoodTableName]) {

            HealthFoodDetail * foodDetail =(HealthFoodDetail *)value;
            NSData * data = [self archiver:foodDetail];

            res = [self.db executeUpdate:@"INSERT INTO food (id, title,data)VALUES (?,?,?)",foodDetail.ID,foodDetail.name,data];
        }
        if ([tableName isEqualToString:KknowledgeTableName]) {
            HealthKnowledgeDetail * knowledgeDetail = (HealthKnowledgeDetail *)value;
            NSData * data = [self archiver:knowledgeDetail];
            
            res = [self.db executeUpdate:@"INSERT INTO knowledge (id, title,data)VALUES (?,?,?)",knowledgeDetail.ID,knowledgeDetail.title,data];
        }
        
        [self.db close];
    }
    
    return res;
}

- (BOOL)deleteWithID:(NSInteger)ID tableName:(NSString *)tableName
{
    BOOL res = NO;
    
    if ([self.db open]) {
        NSString * deleteSql = [NSString stringWithFormat:
                                @"delete from %@ where %@ = %ld",tableName,KID,ID];
        res = [self.db executeUpdate:deleteSql];
        if (!res) {
            NSLog(@"error when delete db table");
        
        } else {
            NSLog(@"success to delete db table");

        }
        [self.db close];
    }
    return res;
}

- (NSArray *)selectAllWithTableName:(NSString *)tableName
{
    
    if ([self.db open]) {
        
        
        NSMutableArray * resultArray = [NSMutableArray array];
        NSString * selectSql = [NSString stringWithFormat: @"SELECT * FROM %@",tableName];
        FMResultSet * rs = [self.db executeQuery:selectSql];
        
        while ([rs next]) {
            //int  ID = [rs intForColumn:KID];
//            NSString * title = [rs stringForColumn:Ktitle];
            
            NSData * data = [rs dataForColumn:Kdata];
             id  value= [self unarchiver:data];
            [resultArray addObject:value];
        }
        
        [self.db close];
        return  resultArray;

        
        
        /*
        if ([tableName isEqualToString: KnewsTableName]) {
            
            while ([rs next]) {
                
                //int  ID = [rs intForColumn:KID];
                //NSString * title = [rs stringForColumn:Ktitle];
                NSData * data = [rs dataForColumn:Kdata];
                HealthNewsDetail * newsDetail = (HealthNewsDetail *)[self unarchiver:data];
                [resultArray addObject:newsDetail];
                [newsDetail release];
            }
            return resultArray;
            
        }
        if ([tableName isEqualToString:KfoodTableName]) {
          
            while ([rs next]) {
                
                //int  ID = [rs intForColumn:KID];
                //NSString * title = [rs stringForColumn:Ktitle];
                NSData * data = [rs dataForColumn:Kdata];
                HealthNewsDetail * newsDetail = (HealthNewsDetail *)[self unarchiver:data];
                [resultArray addObject:newsDetail];
                [newsDetail release];
            }
            return resultArray;

        }
        if ([tableName isEqualToString:KknowledgeTableName]) {
        
            
        }*/
        
    }
      return nil;
}

- (BOOL)existWithID:(NSInteger)ID tableName:(NSString *)tableName
{
    BOOL res = NO;
    
    if ([self.db open]) {
        NSString * selectSql = [NSString stringWithFormat: @"SELECT * FROM %@ where id=%ld",tableName,ID];
        FMResultSet * rs = [self.db executeQuery:selectSql];
        
        int ID = 0;
        while ([rs next]) {
            ID = [rs intForColumn:KID];
        }
        if (ID != 0) {
            res = YES;
        }
        [self.db close];
    }
    return res;
}


- (NSData *)archiver:(id)value{
    
    NSMutableData * data = [NSMutableData dataWithCapacity:20];
    NSKeyedArchiver * archiver = [[NSKeyedArchiver alloc]initForWritingWithMutableData:data];
    [archiver encodeObject:value forKey:KarchiverKey];
    [archiver finishEncoding];
    [archiver release];
    return data;
}

- (id)unarchiver:(NSData *)data{
    
    NSKeyedUnarchiver * unarchiver = [[NSKeyedUnarchiver alloc]initForReadingWithData:data];
    id value = [unarchiver decodeObjectForKey:KarchiverKey];
    [unarchiver finishDecoding];
    [unarchiver release];
    return value;
}
@end
